[Looker][小ネタ]Single ValueのComparisonで”今月1日～昨日までの合計”と”今月1日～一昨日までの合計”の差分を表示してみる #looker

さがらです。

LookerのSingle ValueではComparisonオプションより別の値を表示することが出来ますが、Comparisonはただ別の集計値を表示するだけで、2つの集計値の差分値を表示することが出来ません。（割合は出せますけども。）

今回この問題に関わる内容として、”今月1日～昨日までの合計”と”今月1日～一昨日までの合計”の差分をComparisonとして表示するということをやってみたので、備忘録的に手順をまとめておきます。

対象とするLookML

まず、実装前のLookMLの内容を記しておきます。

このviewファイルで定義されている、sale_priceについて、”今月1日～昨日までの合計”と”今月1日～一昨日までの合計”の差分をComparisonとして表示する方法を次章で説明していきます。

## modelファイル
connection: "aaaaaaaaaa"

named_value_format: yen_0 {
  value_format: "\"¥\"#,##0"
}

explore: order_items_test{}

## viewファイル
view: order_items_test {
  sql_table_name: "PUBLIC"."ORDER_ITEMS";;

  dimension_group: delivered {
    type: time
    timeframes: [
      raw,
      date,
      month
    ]
    sql: ${TABLE}."DELIVERED_AT" ;;
  }

  dimension: sale_price {
    type: number
    sql: ${TABLE}."SALE_PRICE" ;;
  }

  measure: total_sale_price {
    type: sum
    sql: ${sale_price} ;;
    value_format_name: yen_0
  }

}

実装手順

1.LookMLで必要なフィールドを定義する

先程のLookMLのviewファイルに対して、以下の３つのフィールドを追加します。※注意点：接続先はSnowflakeのため、SQLの内容は接続先のDB/DWHに併せて修正してください。

## 上述のviewファイルの末尾に続けて記載

  measure: total_sale_price_until_yesterday{
    type: sum
    value_format_name: yen_0
    sql: CASE WHEN EXTRACT(DAY FROM ${delivered_raw}) < EXTRACT(DAY FROM CURRENT_DATE()) THEN ${sale_price} ELSE NULL END ;;
  }

  measure: total_sale_price_until_2daysago{
    type: sum
    value_format_name: yen_0
    sql: CASE WHEN EXTRACT(DAY FROM ${delivered_raw}) < EXTRACT(DAY FROM CURRENT_DATE())-1 THEN ${sale_price} ELSE NULL END ;;
  }

  measure: total_sales_price_comparison {
    type: number
    value_format_name: yen_0
    sql: ${total_sale_price_until_yesterday} - ${total_sale_price_until_2daysago}  ;;
  }

ポイントは上2つのmeasureそれぞれでCASE文を用いて、「1日～昨日まで」「1日～一昨日まで」、それぞれの期間で集計できるように条件分岐させています。

その後、一番下のmeasureではnumber型を用いて2つのmeasureの差を求めるように定義し、差分値がわかるような仕様としています。

2.ExploreでVisualizeする

2-1.必要なフィールドの選択

下記のように、フィールドを3つ選択します。

• LookMLのCASE文で用いた日付型のdimension
• ”1日～昨日までの合計”を求めるmeasure
• ”1日～昨日までの合計”と”1日～一昨日までの合計”の差分を求めるmeasure

2-2.フィルタの設定

今月のデータだけ表示されれば良いため、LookMLのCASE文で用いた日付型のdimensionに対して、is in the past、1、monthsでフィルタを設定します。

ここまで出来たら、一度実行しましょう！

2-3.Comparisonの設定

後は、下図のようにComparisonの設定をして完成です。

この方法のメリット

私は検証出来ていないですが、type:running_totalのmeasureを上手く使うことで同じことは出来る…かもしれません。

ですが、仮にrunning_totalのmeasureを使うことで実現出来たとしても、今回ご紹介した方法ならではのメリットがあると考えています。

• メリット１：LookMLで記述しているため臨機応変に比較対象を変更できる
  • 比較対象のmeasureをどちらもLookMLで定義しているので、例えば全く別のフィールドを参照したmeasureと比較するように差分値の定義を変更することが出来ます。仕様変更を考えたときに容易に対応できることが、LookMLで定義した場合の強みです。
• メリット２：仮にテーブル計算を用いてしまうと、円マーク用のvalue_formatの記述が都度必要
  • 違う方法で実現しようとしたときにテーブル計算をもし使用していると、円マークの定義がそれぞれのテーブル計算の設定で必要となります。
  • テーブル計算1つならばそこまで大変ではないですが、複数フィールドがあると「単位は円だけど、小数点第二位まで表示させたい…」みたいな仕様変更が発生した時、すべてのタイルを確認して修正する必要があるので、正直やっていられなくなると思います。

最後に

LookMLで実現できる場合には、積極的にLookMLで定義していきましょう！